package com.shujia.batch

import org.apache.spark.sql.SparkSession

object DwsAppVioWsjtj {
  def main(args: Array[String]): Unit = {


    //分区参数
    val ds: String = args.head

    /**
     * 船舰spark sql环境
     *
     */

    val spark: SparkSession = SparkSession
      .builder()
      .appName("DwsAppVioWsjtj")
      .enableHiveSupport() //开启hie元数据支持
      .getOrCreate()

    spark.sql(
      s"""
         |
         |insert overwrite table dws.dws_app_vio_wsjtj partition(ds=$ds)
         |select
         |sbbh, -- 设备编号
         |diff as sjc, -- 无数据天数
         |wfsj as wfsj_day, -- 有数据日期
         |last_wfsj as after_day, -- 上一次有数据日期
         |round(diff /(Q3 + 1.5*(Q3 - Q1)),4) as more_zb --超过占比
         |from (
         |    select
         |    sbbh,wfsj,last_wfsj,diff,diff_rank,
         |    --ceil(sum_num*0.25) as Q1_index,--计算q1位置
         |    --ceil(sum_num*0.75) as Q3_index,--计算q3的位置
         |    -- 如果排名等于Q1对应的索引时才求和，这样计算出来的结果就是Q1的值
         |    sum(case when diff_rank=ceil(sum_num*0.25) then diff else 0 end) over(partition by sbbh) as Q1, --计算q1
         |    sum(case when diff_rank=ceil(sum_num*0.75) then diff else 0 end) over(partition by sbbh) as Q3 --计算q1
         |    from (
         |        select
         |        sbbh,wfsj,last_wfsj,diff,
         |        row_number() over(partition by sbbh order by diff) as diff_rank, --安装时间差排序
         |        count(1) over(partition by sbbh) as sum_num -- 总数
         |        from (
         |           select
         |           sbbh,
         |           wfsj,
         |           last_wfsj,
         |           datediff(wfsj,last_wfsj) - 1  as diff-- 中间没有数据的天使
         |            from (
         |                select
         |                sbbh,
         |                wfsj,
         |                lag(wfsj,1) over (partition by sbbh order by  wfsj) as last_wfsj -- 获取上一次有数据日期
         |                from (
         |                    select
         |                    distinct
         |                    sbbh,
         |                    date_format(wfsj,'yyyy-MM-dd') as  wfsj
         |                    from dwd.dwd_base_vio_surveil
         |                    where ds='$ds'
         |                ) as a
         |           ) as f
         |           -- where datediff(wfsj,last_wfsj) > 1
         |        ) as b
         |    ) as c
         |) as d
         |--取出异常数据，时间戳大于Q3+1.5*IQR
         |where diff > Q3 + 1.5*(Q3 - Q1)
         |
         |""".stripMargin)


  }

}
